﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Threading.Tasks;
using System.Data.SqlClient;
using DAL;

namespace BLL
{
    public class ForBookInfoManager
    {
        //查询基本类
        public SqlDataAdapter SeeBookClass()
        {
            //查询基本类
            string bClass = @"select * from [dbo].[BookClass];";
            SqlDataAdapter adapter = new SqlDataAdapter(bClass, DBHelper.Connection);
            return adapter;
        }
        //查询具体类
        public SqlDataAdapter SeeBookType(int ClassID = -10)
        {
            string type;
            if (ClassID != -10)
            {
                //SQL语句
                type = string.Format(@"select [BookTypeID],[BookType] 
                        from [dbo].[BookType] where ClassID = {0}"
                        , ClassID);
            }
            else
            {
                type = "select [BookTypeID],[BookType] from [dbo].[BookType]";
            }
            SqlDataAdapter adapter = new SqlDataAdapter(type, DBHelper.Connection);
            return adapter;
        }
        //查询图书信息
        public SqlDataAdapter SeeBookInfo()
        {
            string sql = @"select Books.BookID,BookName,BookType,ClassName,Author,Press,State 
                        from Books,BookState,Book
                        inner join BookType on Book.BookTypeID = BookType.BookTypeID
                        inner join BookClass on Book.ClassID = BookClass.ClassID
                        where BookState.StateID = Books.StateID and Book.ID = Books.ID";
            SqlDataAdapter adapter = new SqlDataAdapter(sql, DBHelper.Connection);
            return adapter;
        }
        //查询图书数量
        public int SeeBookNum(int ID)
        {
            StringBuilder sb = new StringBuilder();

            //查询相同的图书数量
            sb.AppendFormat(@"select Number from Book where ID  = Convert(int,(select ID from Books where BookID = '{0}'));", ID);
            int number = Convert.ToInt32(DBHelper.ExecuteScalar(sb.ToString()));
            return number;
        }
        //查询图书编号ID
        public int SeeBooksID(int BookID)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(@"select ID from Books where BookID = {0}", BookID);
            //执行
            int id = Convert.ToInt32(DBHelper.ExecuteScalar(sb.ToString()));
            return id;
        }
        public int DeleteBooks(int BookID)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("delete from Books where BookID = '{0}';", BookID);
            //执行
            int iRet = DBHelper.ExecuteNonQuery(sb.ToString());
            return iRet;
        }
        //当数量为一则直接删除记录
        public int DeleteBook(int ID)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(@"delete from Book where ID = {0}", ID);
            int id = DBHelper.ExecuteNonQuery(sb.ToString());
            return id;
        }
        //当数量大于一则减少数量
        public int UpdateBookNum(int ID)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(@"update Book set Number = Number - 1 where ID = {0};", ID);
            int id = DBHelper.ExecuteNonQuery(sb.ToString());
            return id;
        }
    }
}
